/* Dwarf_Schema.sql */

---------------------------------------------------------------------------------------
create table ejb_dwarf
(
	dwarf_id	int				primary key,
	dwarf_name	varchar(80)	unique		not null,
	born		int				not null,
	home_id		int				null,		/* mountain_id */
	spouse_id	int				null		/* dwarf_id */
);

alter table ejb_dwarf add
	constraint ejb_dwarf_spouse_fk foreign key (spouse_id)
	references ejb_dwarf (dwarf_id);

grant all on ejb_dwarf to public;
\g

create sequence ejb_dwarf_dwarf_id_seq; 
\g

grant all on ejb_dwarf_dwarf_id_seq to public;
\g

---------------------------------------------------------------------------------------
create table ejb_mountain
(
	mountain_id	int				primary key,
	mountain_name	varchar(80)	unique		not null,
	location	varchar(80)			not null,
	king_id		int				null		/* dwarf_id */
);

alter table ejb_dwarf add
	constraint ejb_dwarf_home_fk foreign key (home_id)
	references ejb_mountain (mountain_id);

alter table ejb_mountain add
	constraint ejb_mountain_king_fk foreign key (king_id)
	references ejb_dwarf (dwarf_id);

grant all on ejb_mountain to public;
\g

create sequence ejb_mountain_mountain_id_seq; 
\g

grant all on ejb_mountain_mountain_id_seq to public;
\g

---------------------------------------------------------------------------------------
/* mountains visited by dwarf */
create table ejb_visit
(
	dwarf_id	int		not null,
	mountain_id	int		not null
);

alter table ejb_visit add
	constraint ejb_pk_visit
	primary key (dwarf_id,mountain_id);
grant all on ejb_visit to public;

alter table ejb_visit add
	constraint ejb_visit_dwarf_fk foreign key (dwarf_id)
	references ejb_dwarf (dwarf_id);

alter table ejb_visit add
	constraint ejb_visit_mountain_fk foreign key (mountain_id)
	references ejb_mountain (mountain_id);
\g